﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.Sql;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Common;

namespace Connect
{
  public class Tools
    {
        /// <summary>
        /// Đưa ra kiểu lỗi
        /// </summary>
        public static bool CoLoi = false;
        /// <summary>
        /// Đưa ra chuỗi lỗi
        /// </summary>
        public static string ChuoiLoi = "";
        public static void ThongBaoLoi()
        {
            MessageBox.Show(ChuoiLoi, "Chi tiết lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

  public class cnSQL
    {
       /// <summary>
       /// Lấy danh sách các Server SQL hoạt động trong mạng LAN
       /// </summary>
       /// <returns></returns>
       public static DataTable GetSQLServers()
       {
           ArrayList col = new ArrayList();
           SqlDataSourceEnumerator listServer = SqlDataSourceEnumerator.Instance;
           DataTable dTable = listServer.GetDataSources();
           return dTable;
       }
       public static DataTable GetTable(SqlConnection sqlconnect)
       {
           DataTable tb = new DataTable();
           tb.Columns.Add("Table");
           string cmdText = "SELECT [TABLE_NAME]\r\n FROM INFORMATION_SCHEMA.TABLES\r\n                           WHERE table_type = 'BASE TABLE'";
           SqlConnection connection = sqlconnect;
           SqlCommand command = new SqlCommand(cmdText, connection);
           try
           {
               connection.Open();
               SqlDataReader reader = command.ExecuteReader();
               while (reader.Read())
               {
                   DataRow dr = tb.NewRow();
                    if ((reader[0].ToString() != "sysdiagrams")&&(string.IsNullOrEmpty(reader[0].ToString()))==false)
                   {
                         dr[0]=reader[0].ToString();
                   }
                    tb.Rows.Add(dr);
               }
               return tb;
           }
           catch (SqlException)
           {
               return null;
           }
           finally
           {
               if (connection.State == ConnectionState.Open)
               {
                   connection.Close();
               }
               connection.Dispose();
               command.Dispose();
           }
       }


       #region Lấy thông tin Connection
       public static SqlConnection cn = new SqlConnection();
       public static Connection tempcn = new Connection();

       #region Kiểm tra kết nối tới Server
       /// <summary>
       /// Kiểm tra kết nối tới Server
       /// </summary>
       /// <returns></returns>
       public static bool CheckConnect()
       {
           try
           {
               ServerConnection objSc;
               if (string.IsNullOrEmpty(tempcn.rous()) == false)
               {
                   objSc = new ServerConnection(tempcn.rosv(), tempcn.rous(), tempcn.rops());
               }
               else
               {
                   objSc = new ServerConnection(tempcn.rosv());
               }
               objSc.Connect();
               objSc.Disconnect();
               return true;
           }
           catch
           {
               return false;
           }
       }
       #endregion

       #region Kiểm tra kết nối tới cơ sở dữ liệu
       /// <summary>
       /// Kiểm tra kết nối tới cơ sở dữ liệu
       /// </summary>
       /// <returns></returns>
       public static bool CheckDatabase()
       {
           try
           {
               string StrConnection = "";
               if (string.IsNullOrEmpty(tempcn.rous()) == false)
               {
                   StrConnection = "Server = '" + tempcn.rosv() + "'; Initial Catalog = '" + tempcn.rodb() + "'; user id = '" + tempcn.rous() + "'; pwd = '" + tempcn.rops() + "'";
               }
               else
               {
                   StrConnection = "Server = '" + tempcn.rosv() + "'; Initial Catalog = '" + tempcn.rodb() + "'; Integrated Security=SSPI;";
               }
               SqlConnection SqlCon = new SqlConnection(StrConnection);
               SqlCon.Open();
               SqlCommand Cmd = SqlCon.CreateCommand();
               Cmd.CommandText = "SP_DATABASES";
               SqlDataReader Rd = Cmd.ExecuteReader();
               Rd.Close();
               Rd.Dispose();
               SqlCon.Close();
               SqlCon.Dispose();
               return true;
           }
           catch
           {
               return false;
           }
       }
       public static bool CheckDatabase(string Database)
       {
           try
           {
               string StrConnection = "";
               if (string.IsNullOrEmpty(tempcn.rous()) == false)
               {
                   StrConnection = "Server = '" + tempcn.rosv() + "'; Initial Catalog = '" + Database + "'; user id = '" + tempcn.rous() + "'; pwd = '" + tempcn.rops() + "'";
               }
               else
               {
                   StrConnection = "Server = '" + tempcn.rosv() + "'; Initial Catalog = '" + Database + "'; Integrated Security=SSPI;";
               }
               SqlConnection SqlCon = new SqlConnection(StrConnection);
               SqlCon.Open();
               SqlCommand Cmd = SqlCon.CreateCommand();
               Cmd.CommandText = "SP_DATABASES";
               SqlDataReader Rd = Cmd.ExecuteReader();
               Rd.Close();
               Rd.Dispose();
               SqlCon.Close();
               SqlCon.Dispose();
               return true;
           }
           catch
           {
               return false;
           }
       }
       #endregion
       
       public cnSQL()
       {
           cn = tempcn.GetConnect();
           cn.Open();

       }
             
       #endregion

       #region Thực hiện truy vấn SQL trả về một bảng DataTable
        /// <summary>
       /// Thực hiện truy vấn SQL trả về một bảng DataTable
       /// </summary>
       /// <param name="TruyVan">Câu lệnh SQL </param>
       /// <returns>Bảng kết quả</returns>
       public static DataTable ExecQuery(string TruyVan)
       {
           Tools.CoLoi = false;
           cn = tempcn.GetConnect();
           cn.Open();
           DataTable dt = new DataTable();
           try
           {
               SqlDataAdapter adp = new SqlDataAdapter(TruyVan, cn);
               adp.Fill(dt);
           }
           catch (Exception ex)
           {
               Tools.CoLoi = true;
               Tools.ChuoiLoi = ex.Message;
           }
           return dt;
       }      
       #endregion

       #region Thực thi truy vấn ko trả về kết quả
        /// <summary>
       /// Thực thi truy vấn ko trả về kết quả
       /// </summary>
       /// <param name="TruyVan">Câu lệnh SQL</param>
       /// <returns>Không trả về kết quả</returns>
       public static int ExecNonQuery(string TruyVan)
       {
           Tools.CoLoi = false;
           cn = tempcn.GetConnect();
           cn.Open();
           try
           {
               SqlCommand cm = new SqlCommand();
               cm.CommandText = TruyVan;
               cm.Connection = cn;
               return cm.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
               Tools.CoLoi = true;
               Tools.ChuoiLoi = ex.Message;
               return 0;
           }
       }
       #endregion

       public static string chuanlink(string str)
       {
           int vitri = 0;
           int l = str.Length;

           for (int i = 1; i <= l - 2; i++)
           {
               vitri = str.IndexOf("\\\\");
               if (vitri == -1)
               {
                   break; // TODO: might not be correct. Was : Exit For
               }
               else
               {
                   str = str.Remove(vitri, 1);
               }
           }
           return str;

       }
       public static string Restore(OpenFileDialog opendg, string db)
       {
           string lin = "";
           lin = System.IO.Path.GetDirectoryName(opendg.FileName) + "\\" + opendg.SafeFileName;
           lin = "ALTER DATABASE " + db + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE; restore database " + db + " from disk =N'" + chuanlink(lin) + "';ALTER DATABASE " + db + "   SET MULTI_USER; ";
           return lin;
       }
       public static string Backup(SaveFileDialog savedg, string db)
       {
           Connection h = new Connection();
           string lin = "";
           lin = savedg.FileName;
           lin = "backup database " + db + " to disk ='" + chuanlink(lin) + "'";
           return lin;
       }
       
    }
}
